/**
 * 
 */
package g.dev.xtable;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import javax.swing.JFileChooser;
import javax.swing.JTable;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;

/**
 * @author hdk
 *
 */
public class XTable implements IXTable {

	
	
	private JTable _meTable;
	private Connection _meConnection=null;
	private PreparedStatement _mePs;
	private String _insertSQL="";
	private String _tableName="";
	private String _tablePrimaryKey="";
	private InputStream _inputStream;
	private HSSFWorkbook _workbooks;
	private HSSFSheet _sheet;
	private IXColTypePair _colTypePair;
	
	public XTable(JTable table)
	{
		_meTable=table;
		_colTypePair=new XTableProperties(_meTable.getName());
		
	}

	@Override
	public Boolean ConnectToDB(String dbUrl, String user, String pass) {
		// TODO Auto-generated method stub
		try {
			Class.forName("org.postgresql.Driver");
			_meConnection = DriverManager.getConnection(dbUrl, user, pass);
			return true;
		}
		catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public void CloseConnected() {
		// TODO Auto-generated method stub
		try {
			_meConnection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	

	/* (non-Javadoc)
	 * @see gisc.gov.vn.dev.xtable.IXTable#IsClose()
	 */
	@Override
	public Boolean IsClose() {
		// TODO Auto-generated method stub
		Boolean isclose=false;
		try {
			if(_meConnection==null)
			{
				return true;
			}
			isclose=_meConnection.isClosed();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return true;
		}
		return isclose;
	}

	@Override
	public void ImportFromExcel() {
		// TODO Auto-generated method stub
		JFileChooser chooser = new JFileChooser(System.getProperty("user.dir"));
		chooser.addChoosableFileFilter(new XCustomFilter());
		chooser.setAcceptAllFileFilterUsed(false);
	  
		int state = chooser.showSaveDialog(null); 
	    File file = chooser.getSelectedFile();
	    
	    if (file != null && state == JFileChooser.APPROVE_OPTION )  
        {  
	    	try {
				_inputStream = new FileInputStream(file);
				_workbooks = new HSSFWorkbook(new POIFSFileSystem(_inputStream));
			} 
	    	catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	    	catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	    	_sheet= _workbooks.getSheetAt(0);
        }
	    
	    
	    
//	    if(1==1)
//	    {
	    	
	    	try {
	    	
		    	for (Row row : _sheet) 
		    	{
		    	// SQL = "insert into dvt(ma_dvt,ten_dvt,ghichu_dvt) SELECT ?,?,? WHERE NOT EXISTS (SELECT ma_dvt FROM dvt WHERE ma_dvt=?)";
		    		_insertSQL="INSERT INTO " + _colTypePair.GetTableName();
		    		_insertSQL+="(";
		    		
		    		for(XColTypePair pair : _colTypePair.GetAllPair())
		    		{
		    			_insertSQL+=pair.get_columName()+",";
		    			
		    		}
		    		
		    		_insertSQL+=") ";
		    	}
	    	}
	    	catch (Exception e) {
				// TODO: handle exception
			}
//	    				   _insertSQL = "INSERT INTO internet(ma_internet, ten_chu, banghieu, so_nha, ten_duong,phuong, so_dkkd, ngaycap_dkkd, loaihinh_kd,so_dt,von,ghichu ) ";
//	    				  // AddSQL += " ngaycap_dkkd, so_gcn, ngaycap_gcn, thoihan, ghichu ) ";
//	    				   _insertSQL += " SELECT ?,?,?,?,?,?,?,to_date(?,'DD MM YYYY'),?,?,?,? WHERE NOT EXISTS (SELECT ma_internet FROM internet WHERE ma_internet=?)";
//	    				   
//	    				   
//	    				   _mePs = _meConnection.prepareStatement(_insertSQL);
//						   //ma
//	    				   
//							if (row.getCell(0) != null)
//								_mePs.setInt(1, (int) row.getCell(0).getNumericCellValue());
//							   else 
//								   _mePs.setNull(1,java.sql.Types.INTEGER);
//						
//						   //tenchu
//						   
//							if (row.getCell(1) != null)
//								_mePs.setString(2, (String) row.getCell(1).getStringCellValue());
//							   else 
//								   _mePs.setNull(2, java.sql.Types.CHAR);
//						  //banhhieu
//							
//							if (row.getCell(2) != null)
//								_mePs.setString(3, (String) row.getCell(2).getStringCellValue());
//							   else 
//								   _mePs.setNull(3,java.sql.Types.CHAR);
//													  					
//						
//						   //sonha
//						   try {
//							if (row.getCell(3) != null)
//								_mePs.setInt(4, (int)row.getCell(3).getNumericCellValue());
//							   else 
//								   _mePs.setNull(4,java.sql.Types.CHAR);
//						} catch (Exception e1) {
//							// TODO Auto-generated catch block
//							if (row.getCell(3) != null)
//								_mePs.setString(4, (String) row.getCell(3).getStringCellValue());
//							   else 
//								   _mePs.setNull(4,java.sql.Types.CHAR);
//						}
//						   //tenduong
//						   if (row.getCell(4) != null)
//							   _mePs.setString(5, (String) row.getCell(4).getStringCellValue());
//						   else 
//							   _mePs.setNull(5,java.sql.Types.CHAR);
//						   //tenphuong
//						   if (row.getCell(5) != null)
//							   _mePs.setInt(6, (int) row.getCell(5).getNumericCellValue());
//						   else 
//							   _mePs.setNull(6,java.sql.Types.INTEGER);
//						   //sodkkd
//						  							try {
//								if (row.getCell(6) != null)
//									   ps.setString(7, (String) row.getCell(6).getStringCellValue());
//								   else 
//									   ps.setNull(7,java.sql.Types.CHAR);
//							} catch (Exception e1) {
//								// TODO Auto-generated catch block
//								if (row.getCell(6) != null)
//									   ps.setInt(7, (int) row.getCell(6).getNumericCellValue());
//								   else 
//									   ps.setNull(7,java.sql.Types.CHAR);
//							}
//						
//						   //ngaycap
//						   try {
//							if ((row.getCell(7) != null) && (!row.getCell(7).getStringCellValue().equals("") ))						     
//								   ps.setString(8,row.getCell(7).getStringCellValue());
//							   else 
//								   ps.setNull(8,java.sql.Types.CHAR);
//						} catch (Exception e1) {
//							// TODO Auto-generated catch block
//							JOptionPane.showMessageDialog(null,e1.toString());
//						}
//						   //loaihinh
//						   if (row.getCell(8) != null)
//							   ps.setString(9, (String) row.getCell(8).getStringCellValue());
//						   else 
//							   ps.setNull(9,java.sql.Types.CHAR);
//						   //sodt
//						   if ((row.getCell(9) != null) && (!row.getCell(9).getStringCellValue().equals("") ))						     
//							   ps.setString(10,row.getCell(9).getStringCellValue());
//						   else 
//							   ps.setNull(10,java.sql.Types.CHAR);
//						   //von
//						   if (row.getCell(10) != null)
//							   ps.setInt(11, (int) row.getCell(10).getNumericCellValue());
//						   else 
//							   ps.setNull(11,java.sql.Types.DOUBLE);
//						   //ghichu
//						   if (row.getCell(11) != null)
//							   ps.setString(12, (String) row.getCell(11).getStringCellValue());
//						   else 
//							   ps.setNull(12,java.sql.Types.CHAR);
//						   //magas
//						   try {
//							if (row.getCell(0) != null)
//								   ps.setInt(13, (int) row.getCell(0).getNumericCellValue());
//							   else 
//								   ps.setNull(13,java.sql.Types.INTEGER);
//						} catch (Exception e) {
//							// TODO Auto-generated catch block
//							JOptionPane.showMessageDialog(null,e.toString());
//						}						   	  
//						   ps.executeUpdate(); 
//						   ps.close();
//						   c.close();
//						  					}}
//	    			catch (Exception ex) {
//						// TODO Auto-generated catch block
//	    				JOptionPane.showMessageDialog(null,ex);
//							    		}
//			
//        }
	}

	@Override
	public void ConfigTable(String table_name, String primary_key) {
		// TODO Auto-generated method stub
		_tableName=table_name;
		_tablePrimaryKey=primary_key;
	}

	/* (non-Javadoc)
	 * @see gisc.gov.vn.dev.xtable.IXTable#AddColumn(java.lang.String, java.sql.Types, java.lang.Boolean)
	 */
	@Override
	public void AddColumn(String column_name, Types type, Boolean is_primary_key) {
		// TODO Auto-generated method stub
		_colTypePair.Add(column_name, type);
		if(is_primary_key)
		{
			_tablePrimaryKey=column_name;
		}
		
	}
	
	
}
